forum

home / developersection / forums / parameterizing an sql in clause?

Ask Question

Parameterizing an SQL IN clause?

Anonymous User 2417 06-May-2013
Hi Expert!

How do I parametric a query containing an IN clause with a variable number of arguments, like this one?

select * from Tags 
where Name in ('ruby','rails','scruffy','rubyonrails')
order by Count desc
In this query, the number of arguments could be anywhere from 1 to 5.

I would prefer not to use a dedicated stored procedure for this (or XML), but if there is some fancy SQL Server 2008 specific way of doing it elegantly, I am open to that.

Please help me!

Thanks in advance!

mssql server mssql server 
Updated on 06-May-2013
Anonymous User

Other

I am a content writter !

Message
Can you answer this question?
Answer

1 Answers

AVADHESH PATEL

AVADHESH PATEL

06-May-2013
Hi Ankita!

Here's a quick-and-dirty technique I have used:

select * from Tags
where '|ruby|rails|scruffy|rubyonrails|'
like '%|' + Name + '|%'
So here's the C# code:

string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
const string cmdText = "select * from tags where '|' + @tags + '|' like '%|' + Name + '|%'";

using (SqlCommand cmd = new SqlCommand(cmdText)) {
   cmd.Parameters.AddWithValue("@tags", string.Join("|", tags);
}
Two caveats:

The performance is terrible. like "%...%" queries are not indexed.
Make sure you don't have any |, blank, or null tags or this won't work
There are other ways to accomplish this that some people may consider cleaner, so please keep reading.

advertising
advertising

Liked By

We use cookies to ensure you have the best browsing experience on our website. By using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy